let express = require('express')
let router = express.Router()

let mysqlHelper = require('../utils/mysqlHelper.js')
let fs = require("fs");
// 获取主列表
router.post('/list', async function (req, res, next) {

    let result = {}
    let sqlSt = ''
    try {
        let ifstep4 = false //判断是否查询第4步 并且没有查询第3步 则把第3步的数据也暂时查询出来  筛选出已经有接收人的调度令
        let sqlWhere = ' 1=1 '
        if (req.body.keyword != '' && req.body.keyword != undefined) {
            sqlWhere += " and (LOCATE('" + req.body.keyword + "', ProjectSerrial)>0 \
            or LOCATE('" + req.body.keyword + "', ProjectAuthor)>0 \
            or LOCATE('"+ req.body.keyword + "', ProjectSenderOrg)>0 \
            or LOCATE('" + req.body.keyword + "', ProjectSender)>0 \
            or LOCATE('" + req.body.keyword + "', ProjectSender)>0 \
            or LOCATE('" + req.body.keyword + "', ProjectAuditor2)>0 \
            or LOCATE('" + req.body.keyword + "', ProjectName)>0 )"
        }


        if (req.body.status == -1 || req.body.status == undefined)
            sqlWhere += " and WorkflowID>=1"
        else {
            if (req.body.status.type == -1)
                sqlWhere += " and WorkflowID>=1"
            else {

                if (req.body.status.type.indexOf(4) >= 0 && req.body.status.type.indexOf(3) < 0) {
                    req.body.status.type += ',3'
                    ifstep4 = true
                }
                console.log(req.body.status.type);
                sqlWhere += " and WorkflowID in(" + req.body.status.type + ")"
            }
        }

        //获取角色权限组

        sqlStr = "SELECT  group_concat(c.alias) permission FROM sys_user_role a "
            + " INNER JOIN sys_role_permission b on a.roleId=b.roleId "
            + " INNER JOIN sys_menu c on b.permissionId=c.id "
            + " where a.userId=" + req.body.userId + " and c.alias is not null"

        let permission = (await mysqlHelper.query(sqlStr))[0].permission

        if (req.body.deptId != '' && req.body.deptId != undefined)
            sqlWhere += " and ("
                + " LOCATE('order.verify', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.save', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.process', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.execute', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.terminate', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.archive', concat(',', '" + permission + "')) > 0 "
                + " or LOCATE('order.show', concat(',', '" + permission + "')) > 0 )"

        if (req.body.deptTtype != '' && req.body.deptTtype != undefined) {
            if (req.body.deptTtype == '台站') {
                sqlWhere += " and (LOCATE(concat(',', '" + req.body.deptId + "'), concat(',', stationIds)) > 0 or ProjectSender=" + req.body.userId
                    + " or ProjectAuditor=" + req.body.userId + " or  ProjectSign=" + req.body.userId + "   or ProjectAuditor2 =" + req.body.userId + ")"
            }
        }

        if (req.body.starttime != '' && req.body.endtime != '')
            sqlWhere += " and ProjectStartDate>='" + req.body.starttime + "' and ProjectEndDate<='" + req.body.endtime + "'"

        sqlStr = "select  ProjectID,ProjectSerrial,ProjectType,ProjectAuthor,ProjectSenderOrg,\
        ProjectSender,ProjectAuditor,ProjectStatus,DATE_FORMAT(ProjectStartDate,'%Y-%m-%d %H:%i:%s') as ProjectStartDate,DATE_FORMAT(ProjectEndDate,'%Y-%m-%d %H:%i:%s') as ProjectEndDate,WorkflowID,\
        ProjectName,ProjectAuditor2,ProjectSign,stationIds,UploadFiles,ProjectVerify,Closed,\
        DATE_FORMAT(CreateTime,'%Y-%m-%d %H:%i:%s') CreateTime,DATE_FORMAT(LssuedTime,'%Y-%m-%d %H:%i:%s') xfTime, StatuJson,Auditor from scheme_project where " + sqlWhere


        let total = (await mysqlHelper.query("select count(1) as ct from scheme_project where" + sqlWhere))[0].ct
        if (req.body.pageSize != undefined)
            sqlStr += " order by CreateTime desc limit " + (req.body.page == 1 ? 0 : ((req.body.page - 1) * req.body.pageSize)) + ", " + req.body.pageSize
        else
            sqlStr += " order by CreateTime desc"

        rows = await mysqlHelper.query(sqlStr)

        let users = await mysqlHelper.query("select * from sys_user")
        for (var i = 0; i < rows.length; i++) {
            console.log(i, rows.length);

            let element = rows[i]

            element.children = []
            let userrows = users.filter(user => user.userId == element.ProjectSender)
            if (userrows.length > 0)
                element.ProjectSenderName = userrows[0].name

            userrows = users.filter(user => user.userId == element.ProjectAuditor)
            if (userrows.length > 0)
                element.ProjectAuditorName = userrows[0].name

            userrows = users.filter(user => user.userId == element.ProjectAuditor2)
            if (userrows.length > 0)
                element.ProjectAuditor2Name = userrows[0].name

            userrows = users.filter(user => user.userId == element.ProjectSign)
            if (userrows.length > 0)
                element.ProjectSignName = userrows[0].name

            userrows = users.filter(user => user.userId == element.Auditor)
            if (userrows.length > 0)
                element.AuditorName = userrows[0].name

            //台站信息
            let orgdata = await mysqlHelper.query("select a.*,b.orgName parentName from sys_organization a left join sys_organization b on(a.parentId=b.orgId) ")
            let statujson = []
            if (element.StatuJson != undefined) {
                statujson = JSON.parse(element.StatuJson)

                for (var t = 0; t < statujson.length; t++) {

                    let stationdetail = statujson[t]
                    let org = orgdata.filter(row => row.orgId.toString() == stationdetail.stationID.toString())
                    stationdetail.stationName = org[0]['parentName'] + '-' + org[0]['orgName']

                    if (stationdetail.acceptUser != '') {
                        userrows = users.filter(user => user.userId == stationdetail.acceptUser)
                        if (userrows.length > 0)
                            stationdetail.acceptUserName = userrows[0].name
                        stationdetail.statue = '已接收'
                    }
                    else
                        stationdetail.statue = '未接收'

                    if (stationdetail.executeUser != '') {
                        userrows = users.filter(user => user.userId == stationdetail.executeUser)
                        if (userrows.length > 0)
                            stationdetail.executeUserName = userrows[0].name
                    }

                    if (stationdetail.testUser != '') {
                        userrows = users.filter(user => user.userId == stationdetail.testUser)
                        if (userrows.length > 0)
                            stationdetail.testUserName = userrows[0].name
                    }

                    //告警信息
                    let alarmsql = " SELECT "
                        + " AlertID,"
                        + " SourceDataID,"
                        + " StationID,"
                        + " TransmitterNumber,"
                        + " AnntenaNumber,"
                        + " Frequency,"
                        + " Capacity,"
                        + " b.label as AlertType,"
                        + " time as StartTime,"
                        + " adddate(time, interval AlertDuration second) as EndTime,"
                        + " AlertDuration,"
                        + " AlertContent,"
                        + " DataSnapshot,"
                        + " c.label AS   status"
                        + " FROM scheme_alert a"
                        + " left join "
                        + " (select label,value from sys_dict_detail where dict_id in (select id from sys_dict where code='AlertType')) b on(a.AlertType=b.value)"
                        + " left join "
                        + " (select label,value from sys_dict_detail where dict_id in (select id from sys_dict where code='AlertStatus')) c on(a.STATUS=b.value)"
                        + " where status <> 9"
                        + " and time >= '" + element.ProjectStartDate + "' and time <= '" + element.ProjectEndDate + "' and  StationID in(" + stationdetail.stationID + ")"

                    let alarmdata = await mysqlHelper.query(alarmsql)

                    stationdetail.alarmdata = alarmdata
                    stationdetail.alarmTime = alarmdata.length
                    // if (ifstep4) {
                    //     if(stationdetail.statue = '已接收')
                    // }
                    element.children.push(stationdetail)

                }

                //只选了一个台站 把数据提到上一层
                if (statujson.length == 1) {

                    element.acceptUser = statujson[0].acceptUser
                    element.acceptTime = statujson[0].acceptTime
                    element.executeUser = statujson[0].executeUser //设置执行人
                    element.executeTime = statujson[0].executeTime
                    element.check = statujson[0].check
                    element.execution = statujson[0].execution//执行情况
                    element.alarmTime = statujson[0].alarmTime//告警次数
                    element.alarmdata = statujson[0].alarmdata//告警数据
                    element.emergencyNum = statujson[0].emergencyNum//紧急联系
                    element.stationName = statujson[0].stationName//台站名
                }
            }

            //审核审批人标注 待审核人
            if (element.Auditor != undefined) {

                try {
                    let auditorjson = JSON.parse(element.Auditor)

                    if (element.ProjectAuditor != null && element.ProjectAuditor != '') {
                        if (auditorjson[0].veriytime == '')
                            element.ProjectAuditorName = element.ProjectAuditorName + '(待)'
                        else
                            element.ProjectAuditorName = element.ProjectAuditorName + '(已)'
                    }

                    if (element.ProjectAuditor2 != null && element.ProjectAuditor2 != '') {
                        if (auditorjson.filter(au => au.userId == element.ProjectAuditor2)[0].veriytime == '')
                            element.ProjectAuditor2Name = element.ProjectAuditor2Name + '(待)'
                        else
                            element.ProjectAuditor2Name = element.ProjectAuditor2Name + '(已)'
                    }

                    if (element.ProjectSign != null && element.ProjectSign != '') {
                        if (auditorjson.filter(au => au.userId == element.ProjectSign)[0].veriytime == '')
                            element.ProjectSignName = element.ProjectSignName + '(待)'
                        else
                            element.ProjectSignName = element.ProjectSignName + '(已)'
                    }


                    let nextUser = null
                    for (var j = 0; j < auditorjson.length; j++) {
                        if (auditorjson[j].veriytime == '') {
                            nextUser = auditorjson[j]
                            break
                        }
                    }

                    if (nextUser != null) {
                        element.currentVeriyuserId = nextUser.userId

                        userrows = users.filter(user => user.userId == element.currentVeriyuserId)
                        if (userrows.length > 0)
                            element.currentVeriyuserName = userrows[0].name

                        element.currentVeriyuserType = nextUser.type
                    }
                }
                catch (e) {
                    console.log(e);
                }
            }

        }

        if (ifstep4) {
            let newrows = []
            rows.forEach(element => {
                let ifok = false
                if (element.children != undefined) {
                    element.children.forEach(child => {
                        if (child.statue == '已接收')
                            ifok = true
                    })
                    if (ifok)
                        newrows.push(element)
                }
            })

            rows = newrows
        }
        result = {
            "code": 200,
            "data": {
                "total": total,
                "page": req.body.page,
                "pageSize": req.body.pageSize,
                "rows": rows
            }
            ,
            "message": ""
        }

    } catch (err) {

        result = {
            "code": 400,
            "message": err.message
        }
    }
    res.json(result)
})

// 获取内容
router.post('/content', async function (req, res, next) {
    console.log(req.body);
    let result = {}
    try {

        if (req.body.ProjectID == undefined) {
            result = {
                "code": 200,
                "data": {
                    "total": 0,
                    "page": req.body.page,
                    "pageSize": req.body.pageSize,
                    "rows": []
                }
                ,
                "message": ""
            }
        }
        else if (req.body.ProjectID == -1) {
            result = {
                "code": 200,
                "data": {
                    "total": 0,
                    "page": req.body.page,
                    "pageSize": req.body.pageSize,
                    "rows": []
                }
                ,
                "message": ""
            }
        }
        else {
            let sqlStr = ''
            let total = 0

            if (req.body.activeStep >= 2) {
                let sqlwhere = ''
                if (req.body.deptTtype != undefined) {
                    if (req.body.deptTtype == '台站') {
                        if (req.body.deptId != undefined)
                            sqlwhere += " and (b.orgId=" + req.body.deptId + " or c.ProjectSender=" + req.body.userId
                                + " or c.ProjectAuditor=" + req.body.userId + " or  c.ProjectSign=" + req.body.userId + "   or c.ProjectAuditor2 =" + req.body.userId + ")"
                        else
                            sqlwhere += " and ( c.ProjectSender=" + req.body.userId
                                + " or c.ProjectAuditor=" + req.body.userId + " or  c.ProjectSign=" + req.body.userId + "   or c.ProjectAuditor2 =" + req.body.userId + ")"
                    }
                }

                sqlStr = "select Creator,DataID,TransmitterNumber,AnntenaNumber,ContentID"
                    + ",a.ProjectID"
                    + ",StationID"
                    + ",concat(c.orgName,'-',b.orgName) StationName"
                    + ",Frequency"
                    + ",Capacity"
                    + ",Priority"
                    + ",DurationCount"
                    + ",StartTime1"
                    + ",EndTime1"
                    + ",StartTime2"
                    + ",EndTime2"
                    + ",StartTime3"
                    + ",EndTime3"
                    + ",StartTime4"
                    + ",EndTime4"
                    + ",StartTime5"
                    + ",EndTime5"
                    + ",RepeatType"
                    + ",DATE_FORMAT(RepeatDateBegin,'%Y-%m-%d %H:%i:%s') RepeatDateBegin"
                    + ",DATE_FORMAT(RepeatDateEnd,'%Y-%m-%d %H:%i:%s') RepeatDateEnd"
                    + ",RepeatWeek"
                    + ",RepeatMonth"
                    + ",RepeatDateList"
                    + ",Language"
                    + ",Directly"
                    + ",Program"
                    + ",ThoroughFare"
                    + ",Form"
                    + ",Business"
                    + ",Direction"
                    + ",Service"
                    + ",Oprate"
                    + ",Other"
                    + ",Remarks"
                    + " from scheme_plan a left join sys_organization b on(a.StationID=b.orgId) left join sys_organization c on(b.parentId=c.orgId)"
                    + " left join (select ProjectID,ProjectSender,ProjectAuditor,ProjectAuditor2,ProjectSign from scheme_project where ProjectID=" + req.body.ProjectID + ") c on(a.ProjectID=c.ProjectID)"
                    + " where  a.ProjectID=" + req.body.ProjectID + sqlwhere
                if (req.body.page == -1)
                    sqlStr += " order by StationID"
                else
                    sqlStr += " order by StationID limit " + (req.body.page == 1 ? 0 : ((req.body.page - 1) * req.body.pageSize)) + ", " + req.body.pageSize

                total = (await mysqlHelper.query("select  count(1) as ct from scheme_plan a left join sys_organization b on(a.StationID=b.orgId)"
                    + " left join (select ProjectID,ProjectSender,ProjectAuditor,ProjectAuditor2,ProjectSign from scheme_project where ProjectID=" + req.body.ProjectID + ") c on(a.ProjectID=c.ProjectID)"
                    + " where  a.ProjectID=" + req.body.ProjectID + sqlwhere))[0].ct
            }
            else {
                sqlStr = "select ContentID"
                    + ",ProjectID"
                    + ",StationIDs"
                    + ",Frequency"
                    + ",Capacity"
                    + ",Priority"
                    + ",DurationCount"
                    + ",StartTime1"
                    + ",EndTime1"
                    + ",StartTime2"
                    + ",EndTime2"
                    + ",StartTime3"
                    + ",EndTime3"
                    + ",StartTime4"
                    + ",EndTime4"
                    + ",StartTime5"
                    + ",EndTime5"
                    + ",RepeatType"
                    + ",DATE_FORMAT(RepeatDateBegin,'%Y-%m-%d %H:%i:%s') RepeatDateBegin"
                    + ",DATE_FORMAT(RepeatDateEnd,'%Y-%m-%d %H:%i:%s') RepeatDateEnd"
                    + ",RepeatWeek"
                    + ",RepeatMonth"
                    + ",RepeatDateList"
                    + ",Closed"
                    + ",Language"
                    + ",Directly"
                    + ",Program"
                    + ",ThoroughFare"
                    + ",Form"
                    + ",Business"
                    + ",Direction"
                    + ",Service"
                    + ",Oprate"
                    + ",Other"
                    + ",Remarks"
                    + " from scheme_project_content"
                    + " where  ProjectID=" + req.body.ProjectID
                if (req.body.page == -1)
                    sqlStr += " order by ContentID"
                else
                    sqlStr += " order by ContentID limit " + (req.body.page == 1 ? 0 : ((req.body.page - 1) * req.body.pageSize)) + ", " + req.body.pageSize

                total = (await mysqlHelper.query("select  count(1) as ct from scheme_project_content"
                    + " where  ProjectID=" + req.body.ProjectID))[0].ct
            }

            rows = await mysqlHelper.query(sqlStr)

            rows.forEach(element => {
                element.StartTime1 = element.StartTime1.substr(0, 8)
                element.StartTime2 = element.StartTime2.substr(0, 8)
                element.StartTime3 = element.StartTime3.substr(0, 8)
                element.StartTime4 = element.StartTime4.substr(0, 8)
                element.StartTime5 = element.StartTime5.substr(0, 8)
                element.EndTime1 = element.EndTime1.substr(0, 8)
                element.EndTime2 = element.EndTime2.substr(0, 8)
                element.EndTime3 = element.EndTime3.substr(0, 8)
                element.EndTime4 = element.EndTime4.substr(0, 8)
                element.EndTime5 = element.EndTime5.substr(0, 8)

                element.times = '<div>' + element.StartTime1.toString().substr(0, 8) + ' - ' + element.EndTime1.toString().substr(0, 8) + '</div>'
                if (element.EndTime2.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime2.toString().substr(0, 8) + ' - ' + element.EndTime2.toString().substr(0, 8) + '</div>'
                if (element.EndTime3.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime3.toString().substr(0, 8) + ' - ' + element.EndTime3.toString().substr(0, 8) + '</div>'
                if (element.EndTime4.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime4.toString().substr(0, 8) + ' - ' + element.EndTime4.toString().substr(0, 8) + '</div>'
                if (element.EndTime5.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime5.toString().substr(0, 8) + ' - ' + element.EndTime5.toString().substr(0, 8) + '</div>'

                if (element.RepeatWeek != '')
                    element.repeatarry = element.RepeatWeek
                else if (element.RepeatMonth != '')
                    element.repeatarry = element.RepeatMonth
                else if (element.RepeatDateList != '')
                    element.repeatarry = element.RepeatDateList

            });
            result = {
                "code": 200,
                "data": {
                    "total": total,
                    "page": req.body.page,
                    "pageSize": req.body.pageSize,

                    "rows": rows
                }
                ,
                "message": ""
            }

        }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 获取plan内容
router.post('/plan', async function (req, res, next) {

    let result = {}
    try {

        let sqlWhere = ''
        if (req.body.ProjectID == undefined) {
            result = {
                "code": 200,
                "data": {

                    "rows": []
                }
                ,
                "message": ""
            }
        }
        else if (req.body.ProjectID == -1) {
            result = {
                "code": 200,
                "data": {

                    "rows": []
                }
                ,
                "message": ""
            }
        }
        else {
            if (req.body.deptTtype != '中心') {
                if (req.body.StationID != undefined) {
                    sqlWhere += "  and StationID=" + req.body.StationID
                }
            }
            else
                sqlWhere += "  and StationID in(" + req.body.stationIds + ")"

            let sqlStr = ''
            sqlStr = "select Creator,DataID,TransmitterNumber,AnntenaNumber,ContentID"
                + ",ProjectID"
                + ",StationID"
                + ",concat(c.orgName,'-',b.orgName) StationName"
                + ",Frequency"
                + ",Capacity"
                + ",Priority"
                + ",DurationCount"
                + ",StartTime1"
                + ",EndTime1"
                + ",StartTime2"
                + ",EndTime2"
                + ",StartTime3"
                + ",EndTime3"
                + ",StartTime4"
                + ",EndTime4"
                + ",StartTime5"
                + ",EndTime5"
                + ",RepeatType"
                + ",DATE_FORMAT(RepeatDateBegin,'%Y-%m-%d %H:%i:%s') RepeatDateBegin"
                + ",DATE_FORMAT(RepeatDateEnd,'%Y-%m-%d %H:%i:%s') RepeatDateEnd"
                + ",RepeatWeek"
                + ",RepeatMonth"
                + ",RepeatDateList"
                + ",Language"
                + ",Directly"
                + ",Program"
                + ",ThoroughFare"
                + ",Form"
                + ",Business"
                + ",Direction"
                + ",Service"
                + ",Oprate"
                + ",Other"
                + ",Remarks"
                + " from scheme_plan a left join sys_organization b on(a.StationID=b.orgId) left join sys_organization c on(b.parentId=c.orgId)"
                + " where  ProjectID=" + req.body.ProjectID + sqlWhere

            sqlStr += " order by DataID "
            console.log(sqlStr);
            rows = await mysqlHelper.query(sqlStr)

            rows.forEach(element => {
                element.StartTime1 = element.StartTime1.substr(0, 8)
                element.StartTime2 = element.StartTime2.substr(0, 8)
                element.StartTime3 = element.StartTime3.substr(0, 8)
                element.StartTime4 = element.StartTime4.substr(0, 8)
                element.StartTime5 = element.StartTime5.substr(0, 8)
                element.EndTime1 = element.EndTime1.substr(0, 8)
                element.EndTime2 = element.EndTime2.substr(0, 8)
                element.EndTime3 = element.EndTime3.substr(0, 8)
                element.EndTime4 = element.EndTime4.substr(0, 8)
                element.EndTime5 = element.EndTime5.substr(0, 8)

                element.times = '<div>' + element.StartTime1.toString().substr(0, 8) + ' - ' + element.EndTime1.toString().substr(0, 8) + '</div>'
                if (element.EndTime2.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime2.toString().substr(0, 8) + ' - ' + element.EndTime2.toString().substr(0, 8) + '</div>'
                if (element.EndTime3.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime3.toString().substr(0, 8) + ' - ' + element.EndTime3.toString().substr(0, 8) + '</div>'
                if (element.EndTime4.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime4.toString().substr(0, 8) + ' - ' + element.EndTime4.toString().substr(0, 8) + '</div>'
                if (element.EndTime5.toString().substr(0, 8) != '00:00:00')
                    element.times += '<div>' + element.StartTime5.toString().substr(0, 8) + ' - ' + element.EndTime5.toString().substr(0, 8) + '</div>'

                if (element.RepeatWeek != '')
                    element.repeatarry = element.RepeatWeek
                else if (element.RepeatMonth != '')
                    element.repeatarry = element.RepeatMonth
                else if (element.RepeatDateList != '')
                    element.repeatarry = element.RepeatDateList

            });
            result = {
                "code": 200,
                "data": {

                    "rows": rows
                }
                ,
                "message": ""
            }

        }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 新增
router.post('/add', async function (req, res, next) {

    let result = {}
    try {

        //获取最新id
        let newid = (await mysqlHelper.query("select max(ProjectID)+1 as id from scheme_project "))[0].id
        if (newid == null)
            newid = 1
        //台站json初始化
        let StatuJson = []
        req.body.stationIds.split(',').forEach(element => {
            StatuJson.push({
                stationID: element,
                acceptUser: '',
                acceptTime: '',
                executeUser: '', //设置执行人
                executeTime: '',
                testUser: '', //设置测试人
                testTime: '',
                check: '',//校验
                execution: 0,//执行情况
                alarmTime: 0,//告警次数
                emergencyNum: ''//紧急联系
            })
        });
        let auditor = []
        if (req.body.auditor1Value != '')
            auditor.push(
                {
                    userId: req.body.auditor1Value,
                    type: 'sh',
                    veriytime: ''
                }
            )
        if (req.body.auditor2Value != '')
            auditor.push(
                {
                    userId: req.body.auditor2Value,
                    type: 'sh',
                    veriytime: ''
                }
            )
        if (req.body.signValue != '')
            auditor.push(
                {
                    userId: req.body.signValue,
                    type: 'sp',
                    veriytime: ''
                }
            )
        //#region 插入主表
        let insertsql = "insert into scheme_project ("
            + " ProjectID, "
            + " ProjectSerrial,"
            + " ProjectType,"
            + " ProjectAuthor,"
            + " ProjectSenderOrg,"
            + " ProjectSender,"
            + " ProjectAuditor,"
            + " ProjectStatus,"
            + " ProjectStartDate,"
            + " ProjectEndDate,"
            + " WorkflowID,"
            + " WorkflowStatus,"
            + " Closed,"
            + " ProjectAuditor2,"
            + " ProjectName,"
            + " ProjectSign,"
            + " StationIDs,"
            + " UploadFiles,"
            + " CreateTime,"
            + " Auditor,"
            + " StatuJson"
            + " ) values("
            + newid + ","
            + "'" + req.body.serrialInput + "',"
            + "" + req.body.typeValue + ","
            + "'" + req.body.authorValue + "',"
            + "'" + req.body.senderOrgvalue + "',"
            + "'" + req.body.senderValue + "',"
            + "'" + req.body.auditor1Value + "',"
            + "0,"
            + "'" + req.body.startTime + "',"
            + "'" + req.body.endTime + "',"
            + "1,"
            + "'',"
            + "0,"
            + "'" + req.body.auditor2Value + "',"
            + "'" + req.body.projectName + "',"
            + "'" + req.body.signValue + "',"
            + "'" + req.body.stationIds + "',"
            + "'" + req.body.uploadInput + "',"
            + "now(),"
            + "'" + JSON.stringify(auditor) + "',"
            + "'" + JSON.stringify(StatuJson) + "'"
            + ")"

        await mysqlHelper.query(insertsql)
        //#endregion

        //插入详表
        if (req.body.contentData.length > 0) {

            insertsql = "insert into scheme_project_content("
                // + " ContentID,"
                + " ProjectID,"
                + " StationIDs,"
                + " Frequency,"
                + " Capacity,"
                + " Priority,"
                // + " DurationCount,"
                + " StartTime1,"
                + " EndTime1,"
                + " StartTime2,"
                + " EndTime2,"
                + " StartTime3,"
                + " EndTime3,"
                + " StartTime4,"
                + " EndTime4,"
                + " StartTime5,"
                + " EndTime5,"
                + " RepeatType,"
                + " RepeatDateBegin,"
                + " RepeatDateEnd,"
                + " RepeatWeek,"
                + " RepeatMonth,"
                + " RepeatDateList,"
                + " Closed,"
                + " Language,"
                + " Directly,"
                + " Program,"
                + " ThoroughFare,"
                + " Form,"
                + " Business,"
                + " Direction,"
                + " Service,"
                + " Oprate,"
                + " Other,"
                + " Remarks) values "
            let valuesstring = ""
            req.body.contentData.forEach(element => {

                element.Capacity = element.Capacity == '自动(默认)' ? "0" : element.Capacity
                element.stationInput = element.stationInput == '自动(默认)' ? '' : element.stationInput

                valuesstring += ",("
                    + newid + ","
                    + "'" + element.StationIDs + "',"
                    + "" + element.Frequency + ","
                    + "" + element.Capacity + ","
                    + "" + element.Priority + ","
                    + "'" + element.StartTime1 + "',"
                    + "'" + element.EndTime1 + "',"
                    + "'" + element.StartTime2 + "',"
                    + "'" + element.EndTime2 + "',"
                    + "'" + element.StartTime3 + "',"
                    + "'" + element.EndTime3 + "',"
                    + "'" + element.StartTime4 + "',"
                    + "'" + element.EndTime4 + "',"
                    + "'" + element.StartTime5 + "',"
                    + "'" + element.EndTime5 + "',"
                    + "" + element.RepeatType + ","
                    + "'" + element.RepeatDateBegin + "',"
                    + "'" + element.RepeatDateEnd + "',"
                    + "'" + element.RepeatWeek + "',"
                    + "'" + element.RepeatMonth + "',"
                    + "'" + element.RepeatDateList + "',"
                    + "0,"
                    + "'" + element.Language + "',"
                    + "" + element.Directly + ","
                    + "'" + element.Program + "',"
                    + "" + element.ThoroughFare + ","
                    + "'" + element.Form + "',"
                    + "'" + element.Business + "',"
                    + "" + element.Direction + ","
                    + "'" + element.Service + "',"
                    + "" + element.Oprate + ","
                    + "'" + element.Other + "',"
                    + "'" + element.Remarks + "'"
                    + ")"

            });

            await mysqlHelper.query(insertsql + valuesstring.substr(1))


        }

        //记录流程
        await mysqlHelper.query("insert into sys_flow(ProjectID,"
            + " User,SignDate,SignTitle,SignContent"
            + " )  values(" + newid + "," + req.body.senderValue + ", now(),'中心发起','')")
        result = {
            "code": 200,
            "ProjectID": newid,
            "message": ""
        }


    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 保存主表
router.post('/update', async function (req, res, next) {

    let result = {}
    try {
        //更新主表主表
        let updatesql = "update scheme_project set "
            + " ProjectSerrial=" + "'" + req.body.serrialInput + "',"
            + " ProjectType=" + "" + req.body.typeValue + ","
            + " ProjectAuthor=" + "'" + req.body.authorValue + "',"
            + " ProjectSenderOrg=" + "'" + req.body.senderOrgvalue + "',"
            + " ProjectSender=" + "'" + req.body.senderValue + "',"
            + " ProjectAuditor=" + "'" + req.body.auditor1Value + "',"
            + " ProjectStartDate=" + "'" + req.body.startTime + "',"
            + " ProjectEndDate=" + "'" + req.body.endTime + "',"
            + " ProjectAuditor2=" + "'" + req.body.auditor2Value + "',"
            + " ProjectName=" + "'" + req.body.projectName + "',"
            + " ProjectSign=" + "'" + req.body.signValue + "',"
            + " StationIDs=" + "'" + req.body.stationIds + "',"
            + " UploadFiles=" + "'" + req.body.uploadInput + "'"
            + " where ProjectID=" + req.body.ProjectID

        await mysqlHelper.query(updatesql)

        //更新详表
        if (req.body.contentData.length > 0) {

            if (req.body.activeStep >= 2) {
                //#region 台站接收后操作的是scheme_plan表

                //更新老数据



            }
            else {

                //删除老数据
                await mysqlHelper.query("delete from scheme_project_content where ProjectID=" + req.body.ProjectID)
                //插入新数据
                insertsql = "insert into scheme_project_content("
                    // + " ContentID,"
                    + " ProjectID,"
                    + " StationIDs,"
                    + " Frequency,"
                    + " Capacity,"
                    + " Priority,"
                    // + " DurationCount,"
                    + " StartTime1,"
                    + " EndTime1,"
                    + " StartTime2,"
                    + " EndTime2,"
                    + " StartTime3,"
                    + " EndTime3,"
                    + " StartTime4,"
                    + " EndTime4,"
                    + " StartTime5,"
                    + " EndTime5,"
                    + " RepeatType,"
                    + " RepeatDateBegin,"
                    + " RepeatDateEnd,"
                    + " RepeatWeek,"
                    + " RepeatMonth,"
                    + " RepeatDateList,"
                    + " Closed,"
                    + " Language,"
                    + " Directly,"
                    + " Program,"
                    + " ThoroughFare,"
                    + " Form,"
                    + " Business,"
                    + " Direction,"
                    + " Service,"
                    + " Oprate,"
                    + " Other,"
                    + " Remarks) values "
                let valuesstring = ""
                req.body.contentData.forEach(element => {

                    element.Capacity = element.Capacity == '自动(默认)' ? "0" : element.Capacity

                    valuesstring += ",("
                        + req.body.ProjectID + ","
                        + "'" + element.StationIDs + "',"
                        + "" + element.Frequency + ","
                        + "" + element.Capacity + ","
                        + "" + element.Priority + ","
                        + "'" + element.StartTime1 + "',"
                        + "'" + element.EndTime1 + "',"
                        + "'" + element.StartTime2 + "',"
                        + "'" + element.EndTime2 + "',"
                        + "'" + element.StartTime3 + "',"
                        + "'" + element.EndTime3 + "',"
                        + "'" + element.StartTime4 + "',"
                        + "'" + element.EndTime4 + "',"
                        + "'" + element.StartTime5 + "',"
                        + "'" + element.EndTime5 + "',"
                        + "" + element.RepeatType + ","
                        + "'" + element.RepeatDateBegin + "',"
                        + "'" + element.RepeatDateEnd + "',"
                        + "'" + element.RepeatWeek + "',"
                        + "'" + element.RepeatMonth + "',"
                        + "'" + element.RepeatDateList + "',"
                        + "0,"
                        + "'" + element.Language + "',"
                        + "" + element.Directly + ","
                        + "'" + element.Program + "',"
                        + "" + element.ThoroughFare + ","
                        + "'" + element.Form + "',"
                        + "'" + element.Business + "',"
                        + "" + element.Direction + ","
                        + "'" + element.Service + "',"
                        + "" + element.Oprate + ","
                        + "'" + element.Other + "',"
                        + "'" + element.Remarks + "'"
                        + ")"

                });
                if (valuesstring != "")
                    await mysqlHelper.query(insertsql + valuesstring.substr(1))
            }
        }
        else
            //删除老数据
            await mysqlHelper.query("delete from scheme_project_content where ProjectID=" + req.body.ProjectID)

        //重新编辑保存的要重置执行按钮  可再一次点击执行
        let StatuJson = (await mysqlHelper.query("select StatuJson from scheme_project where ProjectID=" + req.body.ProjectID))[0].StatuJson

        StatuJson = JSON.parse(StatuJson)
        if (StatuJson.length > 0) {
            StatuJson.forEach(element => {
                let ifchange = false
                if (req.body.ifeditall)
                    ifchange = true
                else {
                    if (req.body.deptId == element.stationID)
                        ifchange = true
                }

                if (ifchange) {
                    element.executeUser = ''
                    element.executeTime = ''
                }
            });
        }
        await mysqlHelper.query("update scheme_project set StatuJson='" + JSON.stringify(StatuJson) + "' where ProjectID=" + req.body.ProjectID)


        //记录流程
        // await mysqlHelper.query("insert into sys_flow(ProjectID,"
        //     + " User,SignDate,SignTitle,SignContent"
        //     + " )  values(" + req.body.ProjectID + ",'" + req.body.senderValue + "', now(),'中心发起','')")
        result = {
            "code": 200,
            "ProjectID": req.body.ProjectID,
            "message": ""
        }


    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 新增Content详表
router.post('/addContent', async function (req, res, next) {

    let result = {}
    try {

        //获取最新id
        let newid = (await mysqlHelper.query("select max(ContentID)+1 as id from scheme_project_content "))[0].id
        if (newid == null)
            newid = 1

        //新增到content表
        insertsql = "insert into scheme_project_content("
            + " ContentID,"
            + " ProjectID,"
            + " StationIDs,"
            + " Frequency,"
            + " Capacity,"
            + " Priority,"
            // + " DurationCount,"
            + " StartTime1,"
            + " EndTime1,"
            + " StartTime2,"
            + " EndTime2,"
            + " StartTime3,"
            + " EndTime3,"
            + " StartTime4,"
            + " EndTime4,"
            + " StartTime5,"
            + " EndTime5,"
            + " RepeatType,"
            + " RepeatDateBegin,"
            + " RepeatDateEnd,"
            + " RepeatWeek,"
            + " RepeatMonth,"
            + " RepeatDateList,"
            + " Closed,"
            + " Language,"
            + " Directly,"
            + " Program,"
            + " ThoroughFare,"
            + " Form,"
            + " Business,"
            + " Direction,"
            + " Service,"
            + " Oprate,"
            + " Other,"
            + " Remarks) values "

        let element = req.body
        element.Capacity = element.Capacity == '自动(默认)' ? "0" : element.Capacity

        let valuesstring = "("
            + newid + ","
            + element.ProjectID + ","
            + "'" + element.StationIDs + "',"
            + "" + element.Frequency + ","
            + "" + element.Capacity + ","
            + "" + element.Priority + ","
            + "'" + element.StartTime1 + "',"
            + "'" + element.EndTime1 + "',"
            + "'" + element.StartTime2 + "',"
            + "'" + element.EndTime2 + "',"
            + "'" + element.StartTime3 + "',"
            + "'" + element.EndTime3 + "',"
            + "'" + element.StartTime4 + "',"
            + "'" + element.EndTime4 + "',"
            + "'" + element.StartTime5 + "',"
            + "'" + element.EndTime5 + "',"
            + "" + element.RepeatType + ","
            + "'" + element.RepeatDateBegin + "',"
            + "'" + element.RepeatDateEnd + "',"
            + "'" + element.RepeatWeek + "',"
            + "'" + element.RepeatMonth + "',"
            + "'" + element.RepeatDateList + "',"
            + "0,"
            + "'" + element.Language + "',"
            + "" + element.Directly + ","
            + "'" + element.Program + "',"
            + "" + element.ThoroughFare + ","
            + "'" + element.Form + "',"
            + "'" + element.Business + "',"
            + "" + element.Direction + ","
            + "'" + element.Service + "',"
            + "" + element.Oprate + ","
            + "'" + element.Other + "',"
            + "'" + element.Remarks + "'"
            + ")"

        await mysqlHelper.query(insertsql + valuesstring)


        result = {
            "code": 200,
            "ContentID": newid,
            "message": ""
        }


    } catch (err) {

        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 删除Content
router.post('/deleteContent', async function (req, res, next) {

    let result = {}
    try {
        await mysqlHelper.query("delete from scheme_project_content where ContentID=" + req.body.ContentID)

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 新增Plan详表
router.post('/addPlan', async function (req, res, next) {

    let result = {}
    try {

        //获取最新id
        let newid = (await mysqlHelper.query("select max(DataID)+1 as id from scheme_plan "))[0].id
        if (newid == null)
            newid = 1

        //新增到plan表
        insertsql = "insert into scheme_plan("
            + " Creator,"
            + " DataID,"
            + " ProjectID,"
            + " StationID,"
            + " TransmitterNumber,"
            + " AnntenaNumber,"

            + " Frequency,"
            + " Capacity,"
            + " Priority,"
            + " StartTime1,"
            + " EndTime1,"
            + " StartTime2,"
            + " EndTime2,"
            + " StartTime3,"
            + " EndTime3,"
            + " StartTime4,"
            + " EndTime4,"
            + " StartTime5,"
            + " EndTime5,"
            + " RepeatType,"
            + " RepeatDateBegin,"
            + " RepeatDateEnd,"
            + " RepeatWeek,"
            + " RepeatMonth,"
            + " RepeatDateList,"
            + " Language,"
            + " Directly,"
            + " Program,"
            + " ThoroughFare,"
            + " Form,"
            + " Business,"
            + " Direction,"
            + " Service,"
            + " Oprate,"
            + " Other,"
            + " Remarks) values"


        req.body.Capacity = req.body.Capacity == '自动(默认)' ? 0 : req.body.Capacity
        req.body.AnntenaNumber = req.body.AnntenaNumber == '自动(默认)' ? 0 : req.body.AnntenaNumber
        req.body.TransmitterNumber = req.body.TransmitterNumber == '自动(默认)' ? 0 : req.body.TransmitterNumber
        let valuestring = ''
        console.log(req.body);
        let splitstation = req.body.StationIDs.toString().split(',')
        for (var i = 0; i < splitstation.length; i++) {
            valuestring += ",("
                + req.body.senderValue + ","
                + (newid + i) + ","
                + req.body.ProjectID + ","
                + splitstation[i] + ","
                + req.body.TransmitterNumber + ","
                + req.body.AnntenaNumber + ","

                + "" + req.body.Frequency + ","
                + "" + req.body.Capacity + ","
                + "" + req.body.Priority + ","
                + "'" + req.body.StartTime1 + "',"
                + "'" + req.body.EndTime1 + "',"
                + "'" + req.body.StartTime2 + "',"
                + "'" + req.body.EndTime2 + "',"
                + "'" + req.body.StartTime3 + "',"
                + "'" + req.body.EndTime3 + "',"
                + "'" + req.body.StartTime4 + "',"
                + "'" + req.body.EndTime4 + "',"
                + "'" + req.body.StartTime5 + "',"
                + "'" + req.body.EndTime5 + "',"
                + "" + req.body.RepeatType + ","
                + "'" + req.body.RepeatDateBegin + "',"
                + "'" + req.body.RepeatDateEnd + "',"
                + "'" + req.body.RepeatWeek + "',"
                + "'" + req.body.RepeatMonth + "',"
                + "'" + req.body.RepeatDateList + "',"

                + "'" + req.body.Language + "',"
                + "" + req.body.Directly + ","
                + "'" + req.body.Program + "',"
                + "" + req.body.ThoroughFare + ","
                + "'" + req.body.Form + "',"
                + "'" + req.body.Business + "',"
                + "" + req.body.Direction + ","
                + "'" + req.body.Service + "',"
                + "" + req.body.Oprate + ","
                + "'" + req.body.Other + "',"
                + "'" + req.body.Remarks + "'"
                + ")"
        }


        // console.log(insertsql + valuestring.substr(1) + ";");
        await mysqlHelper.query(insertsql + valuestring.substr(1))


        result = {
            "code": 200,
            "DataID": newid,
            "message": ""
        }


    } catch (err) {

        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 保存详表
router.post('/updateDetail', async function (req, res, next) {

    let result = {}
    try {

        if (req.body.ProjectID == undefined) {
            result = {
                "code": 200,
                "message": ""
            }
        }
        else if (req.body.ProjectID == -1) {
            result = {
                "code": 200,
                "message": ""
            }
        }
        else {
            let sqlStr = ''
            if (req.body.activeStep >= 2) {

                req.body.Capacity = req.body.Capacity == '自动(默认)' ? 0 : req.body.Capacity
                req.body.AnntenaNumber = req.body.AnntenaNumber == '自动(默认)' ? 0 : req.body.AnntenaNumber
                req.body.TransmitterNumber = req.body.TransmitterNumber == '自动(默认)' ? 0 : req.body.TransmitterNumber
                // req.body.AnntenaNumber = req.body.AnntenaNumber == '' ? 0 : req.body.AnntenaNumber
                // req.body.TransmitterNumber = req.body.TransmitterNumber == '自动(默认)' ? 0 : req.body.TransmitterNumber
                sqlStr = "update scheme_plan set "
                    + " TransmitterNumber = " + req.body.TransmitterNumber
                    + ", AnntenaNumber = " + req.body.AnntenaNumber
                    + ",Capacity = " + req.body.Capacity
                    + ",StartTime1 = '" + req.body.StartTime1 + "'"
                    + ",StartTime2 = '" + req.body.StartTime2 + "'"
                    + ",StartTime3 = '" + req.body.StartTime3 + "'"
                    + ",StartTime4 = '" + req.body.StartTime4 + "'"
                    + ",StartTime5 = '" + req.body.StartTime5 + "'"
                    + ",EndTime1 = '" + req.body.EndTime1 + "'"
                    + ",EndTime2 = '" + req.body.EndTime2 + "'"
                    + ",EndTime3 = '" + req.body.EndTime3 + "'"
                    + ",EndTime4 = '" + req.body.EndTime4 + "'"
                    + ",EndTime5 = '" + req.body.EndTime5 + "',"
                    + " Priority = " + req.body.Priority + ","
                    + " RepeatType= " + req.body.RepeatType + ","
                    + " RepeatDateBegin= '" + req.body.RepeatDateBegin + "',"
                    + " RepeatDateEnd= '" + req.body.RepeatDateEnd + "',"
                    + " RepeatWeek= '" + req.body.RepeatWeek + "',"
                    + " RepeatMonth= '" + req.body.RepeatMonth + "',"
                    + " RepeatDateList= '" + req.body.RepeatDateList + "',"
                    + " Language= '" + req.body.Language + "',"
                    + " Directly= " + req.body.Directly + ""

                    + ",Program = '" + req.body.Program + "'"
                    + ",ThoroughFare = " + req.body.ThoroughFare
                    + ",Form = '" + req.body.Form + "'"
                    + ",Business = '" + req.body.Business + "'"
                    + ",Direction = " + req.body.Direction
                    + ",Service = '" + req.body.Service + "'"
                    + ",Oprate = " + req.body.Oprate
                    + ",Other = '" + req.body.Other + "'"
                    + ",Remarks = '" + req.body.Remarks + "'"
                    + " where  DataID=" + req.body.DataID

                await mysqlHelper.query(sqlStr)


                result = {
                    "code": 200,
                    "message": ""
                }
            }
            else {
                sqlStr = "update scheme_project_content set"
                    + " StationIDs= '" + req.body.StationIDs + "',"
                    + " Frequency= " + req.body.Frequency + ","
                    + " Capacity= " + req.body.Capacity + ","
                    + " Priority= " + req.body.Priority + ","
                    + " StartTime1= '" + req.body.StartTime1 + "',"
                    + " EndTime1= '" + req.body.EndTime1 + "',"
                    + " StartTime2= '" + req.body.StartTime2 + "',"
                    + " EndTime2= '" + req.body.EndTime2 + "',"
                    + " StartTime3= '" + req.body.StartTime3 + "',"
                    + " EndTime3= '" + req.body.EndTime3 + "',"
                    + " StartTime4= '" + req.body.StartTime4 + "',"
                    + " EndTime4= '" + req.body.EndTime4 + "',"
                    + " StartTime5= '" + req.body.StartTime5 + "',"
                    + " EndTime5= '" + req.body.EndTime5 + "',"
                    + " RepeatType= " + req.body.RepeatType + ","
                    + " RepeatDateBegin= '" + req.body.RepeatDateBegin + "',"
                    + " RepeatDateEnd= '" + req.body.RepeatDateEnd + "',"
                    + " RepeatWeek= '" + req.body.RepeatWeek + "',"
                    + " RepeatMonth= '" + req.body.RepeatMonth + "',"
                    + " RepeatDateList= '" + req.body.RepeatDateList + "',"

                    + " Language= '" + req.body.Language + "',"
                    + " Directly= " + req.body.Directly + ","
                    + " Program= '" + req.body.Program + "',"
                    + " ThoroughFare= " + req.body.ThoroughFare + ","
                    + " Form= '" + req.body.Form + "',"
                    + " Business= '" + req.body.Business + "',"
                    + " Direction= " + req.body.Direction + ","
                    + " Service= '" + req.body.Service + "',"
                    + " Oprate= " + req.body.Oprate + ","
                    + " Other= '" + req.body.Other + "',"
                    + " Remarks= '" + req.body.Remarks + "',"
                    + "Capacity = " + req.body.Capacity
                    + " where  ContentID=" + req.body.ContentID

                await mysqlHelper.query(sqlStr)
                result = {
                    "code": 200,

                    "message": ""
                }
                result = {
                    "code": 200,

                    "message": ""
                }
            }
        }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)

})

// 删除
router.post('/delete', async function (req, res, next) {

    let result = {}
    try {
        await mysqlHelper.query("delete from scheme_project where ProjectID=" + req.body.id)
        await mysqlHelper.query("delete from scheme_project_content where ProjectID=" + req.body.id)
        await mysqlHelper.query("delete from scheme_plan where ProjectID=" + req.body.id)
        await mysqlHelper.query("delete from sys_flow where ProjectID=" + req.body.id)
        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 获取流程记录
router.post('/flow', async function (req, res, next) {

    let result = {}
    try {

        let sqlStr = "select  "
            + "ProjectID,"
            + "FlowID,"
            + "b.name User,"
            + "DATE_FORMAT(SignDate,'%Y-%m-%d %H:%i:%s') SignDate,"
            + "SignTitle,"
            + "SignContent from sys_flow a left join sys_user b on(a.User=b.userId) where a.ProjectID =" + req.body.ProjectID

        rows = await mysqlHelper.query(sqlStr)

        result = {
            "code": 200,
            "data": rows,
            "message": ""
        }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }
    }
    res.json(result)
})

// 审核
router.post('/verify', async function (req, res, next) {

    let result = {}
    try {
        let veriytype = '审核'
        if (req.body.type != undefined)
            veriytype = req.body.type
        await mysqlHelper.query("insert into sys_flow(ProjectID,"
            + " User,SignDate,SignTitle,SignContent"
            + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'" + veriytype + "意见','" + req.body.content + "')")

        let nextUser = 0
        if (req.body.ifthroug == '1') {

            let Auditor = (await mysqlHelper.query("select Auditor from scheme_project where ProjectID=" + req.body.ProjectID))[0].Auditor
            Auditor = JSON.parse(Auditor)
            Auditor.forEach(element => {
                if (element.userId == req.body.sender)
                    element.veriytime = req.body.actime
            });

            let ifallchecked = true
            for (var i = 0; i < Auditor.length; i++) {
                if (Auditor[i].veriytime == '') {
                    ifallchecked = false
                    nextUser = Auditor[i].userId
                    break
                }
            }
            if (ifallchecked) {
                //已经全部审批审核完毕
                await mysqlHelper.query("update scheme_project set WorkflowID=3 ,Auditor='" + JSON.stringify(Auditor) + "',ProjectVerify='" + req.body.ifthroug + "' ,LssuedTime=now() where ProjectID=" + req.body.ProjectID)

                req.body.stationIds.split(',').forEach(async (element) => {
                    //新增到plan表
                    insertsql = "insert into scheme_plan("
                        + " Creator,"
                        + " ContentID,"
                        + " ProjectID,"
                        + " StationID,"
                        + " Frequency,"
                        + " Capacity,"
                        + " Priority,"
                        + " StartTime1,"
                        + " EndTime1,"
                        + " StartTime2,"
                        + " EndTime2,"
                        + " StartTime3,"
                        + " EndTime3,"
                        + " StartTime4,"
                        + " EndTime4,"
                        + " StartTime5,"
                        + " EndTime5,"
                        + " RepeatType,"
                        + " RepeatDateBegin,"
                        + " RepeatDateEnd,"
                        + " RepeatWeek,"
                        + " RepeatMonth,"
                        + " RepeatDateList,"
                        + " Language,"
                        + " Directly,"
                        + " Program,"
                        + " ThoroughFare,"
                        + " Form,"
                        + " Business,"
                        + " Direction,"
                        + " Service,"
                        + " Oprate,"
                        + " Other,"
                        + " Remarks) select  "
                        + req.body.sender + ","
                        + " ContentID,"
                        + " ProjectID,"
                        + element + ","
                        + " Frequency,"
                        + " Capacity,"
                        + " Priority,"
                        + " StartTime1,"
                        + " EndTime1,"
                        + " StartTime2,"
                        + " EndTime2,"
                        + " StartTime3,"
                        + " EndTime3,"
                        + " StartTime4,"
                        + " EndTime4,"
                        + " StartTime5,"
                        + " EndTime5,"
                        + " RepeatType,"
                        + " RepeatDateBegin,"
                        + " RepeatDateEnd,"
                        + " RepeatWeek,"
                        + " RepeatMonth,"
                        + " RepeatDateList,"
                        + " Language,"
                        + " Directly,"
                        + " Program,"
                        + " ThoroughFare,"
                        + " Form,"
                        + " Business,"
                        + " Direction,"
                        + " Service,"
                        + " Oprate,"
                        + " Other,"
                        + " Remarks from scheme_project_content where ProjectID=" + req.body.ProjectID + " and LOCATE(concat(',','" + element + "'),concat(',', StationIDs))>0"
                    console.log(insertsql);
                    await mysqlHelper.query(insertsql)
                })

                //发送待办消息
                req.body.stationIds.split(',').forEach(async (element) => {
                    rows = await mysqlHelper.query("select a.userId  from sys_user a left join sys_organization b on(a.orgId=b.orgId) where b.orgId=" + element)
                    if (rows.length > 0) {
                        for (var i = 0; i < rows.length; i++) {


                            let sql = "insert into sys_message("
                                + "type"
                                + ",re_user"
                                + ",send_user"
                                + ",title"
                                + ",link"
                                + ",time"
                                + ",if_read"
                                + ",content) values("
                                + "'db',"
                                + "" + rows[i]['userId'] + ","
                                + "" + req.body.sender + ","
                                + "'',"
                                + "'orderlist/3',"
                                + "now(),"
                                + "0,"
                                + "'【" + req.body.ProjectSerrial + "】 待接收'"
                                + ")"

                            await mysqlHelper.query(sql)

                        };
                        // await mysqlHelper.query("insert into sys_flow(ProjectID,"
                        //     + " User,SignDate,SignTitle,SignContent"
                        //     + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'催办','" + req.body.content + "')")

                        result = {
                            "code": 200,
                            "message": ""
                        }
                    }
                })
            }
            else {
                await mysqlHelper.query("update scheme_project set Auditor='" + JSON.stringify(Auditor) + "' ,LssuedTime=now() where ProjectID=" + req.body.ProjectID)
                //通知下一个审核人
                let vtype = nextUser.type == 'sh' ? '审核' : '审批'
                await mysqlHelper.query("insert into sys_message("
                    + "type"
                    + ",re_user"
                    + ",send_user"
                    + ",title"
                    + ",link"
                    + ",time"
                    + ",if_read"
                    + ",content) values("
                    + "'db',"
                    + "" + nextUser + ","
                    + "" + req.body.sender + ","
                    + "'',"
                    + "'orderlist/2',"
                    + "now(),"
                    + "0,"
                    + "'【" + req.body.ProjectSerrial + "】 待" + vtype + "'"
                    + ")")

            }
        }
        else {
            let Auditor = (await mysqlHelper.query("select Auditor from scheme_project where ProjectID=" + req.body.ProjectID))[0].Auditor
            Auditor = JSON.parse(Auditor)
            Auditor.forEach(element => {
                element.veriytime = ''
            });

            await mysqlHelper.query("update scheme_project set  Auditor='" + JSON.stringify(Auditor) + "' ,WorkflowID=1 ,ProjectVerify='" + req.body.ifthroug + "'  where ProjectID=" + req.body.ProjectID)
        }

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 提交审核
router.post('/submit', async function (req, res, next) {

    let result = {}
    try {
        await mysqlHelper.query("update scheme_project set WorkflowID=2 where ProjectID=" + req.body.ProjectID)

        await mysqlHelper.query("insert into sys_flow(ProjectID,"
            + " User,SignDate,SignTitle,SignContent"
            + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'提交审核','提交审核')")

        await mysqlHelper.query("insert into sys_message("
            + "type"
            + ",re_user"
            + ",send_user"
            + ",title"
            + ",link"
            + ",time"
            + ",if_read"
            + ",content) values("
            + "'db',"
            + "" + req.body.ProjectAuditor + ","
            + "" + req.body.sender + ","
            + "'',"
            + "'orderlist/2',"
            + "now(),"
            + "0,"
            + "'【" + req.body.ProjectSerrial + "】 待审核'"
            + ")")

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 台站接收
router.post('/accept', async function (req, res, next) {

    let result = {}
    try {
        let statujson = (await mysqlHelper.query("select StatuJson from scheme_project where ProjectID=" + req.body.ProjectID))[0].StatuJson
        statujson = JSON.parse(statujson)
        let jsonrows = statujson.filter(row => row.stationID.toString() == req.body.stationID.toString())
        if (jsonrows.length > 0) {
            jsonrows[0].acceptUser = req.body.sender
            jsonrows[0].acceptTime = req.body.actime

            await mysqlHelper.query("insert into sys_flow(ProjectID,"
                + " User,SignDate,SignTitle,SignContent"
                + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'台站接收','')")


            //判断是否所有的台站都已经接收，然后进入下一步
            let ifallaccepted = true
            statujson.forEach(element => {
                if (element.acceptUser == '' || element.acceptUser == null)
                    ifallaccepted = false
            });
            if (ifallaccepted) {
                await mysqlHelper.query("update scheme_project set WorkflowID=4 , StatuJson='" + JSON.stringify(statujson) + "'  where ProjectID=" + req.body.ProjectID)
                result = {
                    "code": 200,
                    "message": "",
                    "WorkflowID": 4
                }
            }
            else {
                await mysqlHelper.query("update scheme_project set  StatuJson='" + JSON.stringify(statujson) + "'  where ProjectID=" + req.body.ProjectID)
                result = {
                    "code": 200,
                    "message": ""
                }
            }



        }
        else
            result = {
                "code": 400,
                "message": "未找到对应的台站"
            }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 删除plan
router.post('/deletePlan', async function (req, res, next) {

    let result = {}
    try {
        await mysqlHelper.query("delete from scheme_plan where DataID=" + req.body.DataID)

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 执行
router.post('/execute', async function (req, res, next) {

    let result = {}
    try {
        let statujson = (await mysqlHelper.query("select StatuJson from scheme_project where ProjectID=" + req.body.ProjectID))[0].StatuJson
        statujson = JSON.parse(statujson)
        let jsonrows = statujson.filter(row => row.stationID.toString() == req.body.stationID.toString())
        if (jsonrows.length > 0) {
            jsonrows[0].executeUser = req.body.sender
            jsonrows[0].executeTime = req.body.extime

            await mysqlHelper.query("insert into sys_flow(ProjectID,"
                + " User,SignDate,SignTitle,SignContent"
                + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'分配执行','" + req.body.content + "')")

            //判断是否所有的台站都已经执行，然后进入下一步
            let ifallexecuted = true
            statujson.forEach(element => {
                if (element.executeUser == '' || element.executeUser == null)
                    ifallexecuted = false
            });
            if (ifallexecuted) {
                await mysqlHelper.query("update scheme_project set WorkflowID=5 , StatuJson='" + JSON.stringify(statujson) + "'  where ProjectID=" + req.body.ProjectID)
                result = {
                    "code": 200,
                    "message": "",
                    "WorkflowID": 4
                }
            }

            else {
                await mysqlHelper.query("update scheme_project set  StatuJson='" + JSON.stringify(statujson) + "'  where ProjectID=" + req.body.ProjectID)
                result = {
                    "code": 200,
                    "message": ""
                }
            }



        }
        else
            result = {
                "code": 400,
                "message": "未找到对应的台站"
            }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 测试
router.post('/test', async function (req, res, next) {

    let result = {}
    try {
        let statujson = (await mysqlHelper.query("select StatuJson from scheme_project where ProjectID=" + req.body.ProjectID))[0].StatuJson
        statujson = JSON.parse(statujson)
        let jsonrows = statujson.filter(row => row.stationID.toString() == req.body.stationID.toString())
        if (jsonrows.length > 0) {
            jsonrows[0].testUser = req.body.sender
            jsonrows[0].testTime = req.body.extime

            await mysqlHelper.query("insert into sys_flow(ProjectID,"
                + " User,SignDate,SignTitle,SignContent"
                + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'应急测试','" + req.body.content + "')")

            await mysqlHelper.query("update scheme_project set  StatuJson='" + JSON.stringify(statujson) + "'  where ProjectID=" + req.body.ProjectID)
            result = {
                "code": 200,
                "message": ""
            }

        }
        else
            result = {
                "code": 400,
                "message": "未找到对应的台站"
            }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 中止
router.post('/stop', async function (req, res, next) {

    let result = {}
    try {

        await mysqlHelper.query("insert into sys_flow(ProjectID,"
            + " User,SignDate,SignTitle,SignContent"
            + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'中止调度','" + req.body.content + "')")

        await mysqlHelper.query("update scheme_project set WorkflowID=6,Closed=1,ProjectStatus=9   where ProjectID=" + req.body.ProjectID)

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 消息发送
router.post('/sendMsg', async function (req, res, next) {

    let result = {}
    try {
        //rows = await mysqlHelper.query("select * fom sys_user where userId=" + req.body.re_user)[0]
        let valuesql = ''
        let userids = ''
        for (let i = 0; i < req.body.re_user.length; i++) {
            valuesql += ",("
                + "'" + req.body.type + "',"
                + "" + req.body.re_user[i][req.body.re_user[i].length - 1] + ","
                + "" + req.body.sender + ","
                + "'',"
                + "'',"
                + "now(),"
                + "0,"
                + "'" + req.body.content + "'"
                + ")"
            userids += ',' + req.body.re_user[i][req.body.re_user[i].length - 1]
        }
        let sql = "insert into sys_message("
            + "type"
            + ",re_user"
            + ",send_user"
            + ",title"
            + ",link"
            + ",time"
            + ",if_read"
            + ",content) values " + valuesql.substr(1)

        // values("
        //     + "'" + req.body.type + "',"
        //     + "" + req.body.re_user + ","
        //     + "" + req.body.sender + ","
        //     + "'',"
        //     + "'',"
        //     + "now(),"
        //     + "0,"
        //     + "'" + req.body.content + "'"
        //     + ")"

        await mysqlHelper.query(sql)
        let reuser = (await mysqlHelper.query("select GROUP_CONCAT(name) names from sys_user where userId in(" + userids.substr(1) + ")"))[0].names
        await mysqlHelper.query("insert into sys_flow(ProjectID,"
            + " User,SignDate,SignTitle,SignContent"
            + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'催办','向【" + reuser + "】发起催办')")

        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 发送消息给台站
router.post('/sendMsgToDept', async function (req, res, next) {
    console.log(req.body);
    let result = {}
    try {
        rows = await mysqlHelper.query("select a.userId  from sys_user a left join sys_organization b on(a.orgId=b.orgId) where b.orgId=" + req.body.re_dept)
        if (rows.length > 0) {
            for (var i = 0; i < rows.length; i++) {


                let sql = "insert into sys_message("
                    + "type"
                    + ",re_user"
                    + ",send_user"
                    + ",title"
                    + ",link"
                    + ",time"
                    + ",if_read"
                    + ",content) values("
                    + "'" + req.body.type + "',"
                    + "" + rows[i]['userId'] + ","
                    + "" + req.body.sender + ","
                    + "'',"
                    + "'',"
                    + "now(),"
                    + "0,"
                    + "'" + req.body.content + "'"
                    + ")"

                await mysqlHelper.query(sql)

            };
            await mysqlHelper.query("insert into sys_flow(ProjectID,"
                + " User,SignDate,SignTitle,SignContent"
                + " )  values(" + req.body.ProjectID + "," + req.body.sender + ", now(),'催办','" + req.body.content + "')")

            result = {
                "code": 200,
                "message": ""
            }
        }
        else {
            result = {
                "code": 400,
                "message": '此部门下未设置接收人'
            }
        }

    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 消息列表
router.get('/msgList', async function (req, res, next) {

    let result = {}
    try {

        let sql = "SELECT"
            + " a.id,a.link,"
            + " a.type,"
            + " b.avatar,"
            + " concat(c.orgName,'--',b.NAME) title,"
            + " DATE_FORMAT( a.time, '%Y-%m-%d %H:%i:%s' ) time,"
            + " a.content "
            + " FROM"
            + " sys_message a"
            + " LEFT JOIN sys_user b ON ( a.re_user = b.userId )"
            + " left join sys_organization c on(b.orgId=c.orgId)"
            + " where a.re_user=" + req.query.userid + " and if_read=0"
        rows = await mysqlHelper.query(sql)

        result = {
            "code": 200,
            "data": rows,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 消息已读
router.post('/markRead', async function (req, res, next) {

    let result = {}
    try {

        let sql = "update sys_message set if_read=1 where id in(" + req.body.ids + ")"

        await mysqlHelper.query(sql)


        result = {
            "code": 200,
            "message": ""
        }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 获取紧急联系人
router.get('/contactor', async function (req, res, next) {

    let result = {}
    try {
        rows = await mysqlHelper.query("SELECT contactorIds from sys_organization where orgId=" + req.query.stationID)
        if (rows.length > 0) {
            userrows = await mysqlHelper.query("SELECT name,phone from sys_user where userId in(" + rows[0]['contactorIds'] + ")")
            if (userrows.length > 0) {

                result = {
                    "code": 200,
                    "data": userrows,
                    "message": ""
                }
            }
            else
                result = {
                    "code": 400,
                    "message": "此台站下未查询到紧急联系人"
                }
        }
        else
            result = {
                "code": 400,
                "message": "未查询到相关台站信息"
            }
    } catch (err) {
        result = {
            "code": 400,
            "message": err.message
        }

    }
    res.json(result)
})

// 获取告警
router.post('/getalarm', async function (req, res, next) {

    let result = {}

    try {

        rows = await mysqlHelper.query(" SELECT "
            + " AlertID,"
            + " SourceDataID,"
            + " StationID,"
            + " TransmitterNumber,"
            + " AnntenaNumber,"
            + " Frequency,"
            + " Capacity,"
            + " b.label as AlertType,"
            + " time as StartTime,"
            + " adddate(time, interval AlertDuration second) as EndTime,"
            + " AlertDuration,"
            + " AlertContent,"
            + " DataSnapshot,"
            + " c.label AS   status"
            + " FROM scheme_alert a"
            + " left join "
            + " (select label,value from sys_dict_detail where dict_id in (select id from sys_dict where code='AlertType')) b on(a.AlertType=b.value)"
            + " left join "
            + " (select label,value from sys_dict_detail where dict_id in (select id from sys_dict where code='AlertStatus')) c on(a.STATUS=b.value)"
            + " where status <> 9"
            + " and time >= '" + req.body.datetime + " 00:00:00' and time <= '" + req.body.datetime + " 23:59:59'")
        result = {
            "code": 200,
            "data": rows,
            "message": ""
        }

    } catch (err) {

        result = {
            "code": 400,
            "message": err.message
        }
    }
    res.json(result)
})

// 文件上传
// router.post('/upload', async function (req, res, next) {

//     let result = {}
//     try {
//         let file = req.files[0]
//         let oldName = file.path;//获取名字
//         console.log(file);
//         let newName = 'download\\' + file.originalname

//         fs.rename(oldName, newName, (err) => {

//             if (err) {
//                 result = {
//                     "code": 400,
//                     "message": err.message
//                 }
//             } else {
//                 result = {
//                     "code": 200,
//                     "message": ""
//                 }
//             }
//         })

//     } catch (err) {
//         result = {
//             "code": 400,
//             "message": err.message
//         }
//     }
//     res.json(result)
// })

// 文件下载
router.get('/downloadFile', async function (req, res, next) {

    let result = {}
    try {

        let path = './download/' + req.query.filename
        let encodename = encodeURIComponent(req.query.filename)
        if (fs.existsSync(path)) {
            // 创建可读流，读取当前项目目录下的文件
            var rs = fs.createReadStream(path);

            // 设置响应请求头，200表示成功的状态码，headers表示设置的请求头
            res.writeHead(200, {
                'Content-Type': 'application/octet-stream',
                'Content-Disposition': 'attachment; filename=' + encodename
            });
            // 将可读流传给响应对象response

            rs.pipe(res);
            // result = {
            //     "code": 200,
            //     "message": ''
            // }
            // fs.readFile(path, (err, data) => {
            //     if (err)
            //         res.json({
            //             "code": 400,
            //             "message": err.message
            //         })
            //     res.writeHead(200, {
            //         'Content-Type': 'application/force-download',
            //         'Content-Disposition': 'attachment; filename=' + encodename
            //     });
            //     res.end(data)
            // })

        }
        else {

            result = {
                "code": 400,
                "message": '文件不存在'
            }
            res.json(result)
        }

    } catch (err) {

        result = {
            "code": 400,
            "message": err.message
        }
        res.json(result)
    }

})

module.exports = router
